package com.mstore.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.mstore.pojo.Consume;

public class ConsumeDao extends SuperDao {

	public void add(Consume consume) {
		super.save(consume);
	}

	public List<Object> getConsumePage(int index, Consume consume) {
		/*String sql = "from Consume where shopId = " + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where shopId = ");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");

		List<Object> objcts = super.findPage(index, sb.toString());
		return objcts;
		
	}

	public List<Object> getEnterpriseConsumePage(int index, Consume consume) {
		/*String sql = "from Consume where eaId = " + consume.getEaId()
				+ " order by createTime desc"*/;
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.findPage(index, sb.toString());
		return objcts;
	}

	public int getConsumeLine(Consume consume) {
		String sql = "from Consume where shopId=? and eaId = ?";
		List<Object> objects = super.getObject(sql,
				new Object[] { consume.getShopId(), consume.getEaId() });
		return objects.size();
	}

	public int getEnterpriseConsumeLine(Consume consume) {
		String sql = "from Consume where eaId = ?";
		List<Object> objects = super.getObject(sql,
				new Object[] { consume.getEaId() });
		return objects.size();
	}

	public int getOneConsumeLine(Consume consume) {
		String sql = "from Consume where shopId=? and eaId = ? and memberId = ?";
		List<Object> objects = super.getObject(
				sql,
				new Object[] { consume.getShopId(), consume.getEaId(),
						consume.getMemberId() });
		return objects.size();
	}

	public int getOneConsumeLineByMobileAndShop(Consume consume) {
		String sql = "from Consume where shopId=? and eaId = ? and memberMobile = ?";
		List<Object> objects = super.getObject(
				sql,
				new Object[] { consume.getShopId(), consume.getEaId(),
						consume.getMemberMobile() });
		return objects.size();
	}

	public int getEnterpriseOneConsumeLine(Consume consume) {
		String sql = "from Consume where eaId = ? and memberId = ?";
		List<Object> objects = super.getObject(sql,
				new Object[] { consume.getEaId(), consume.getMemberId() });
		return objects.size();
	}

	public int getOneConsumeLineByMobile(Consume consume) {
		String sql = "from Consume where eaId = ? and memberMobile = ?";
		List<Object> objects = super.getObject(sql,
				new Object[] { consume.getEaId(), consume.getMemberMobile() });
		return objects.size();
	}

	public List<Object> getConsumes(Consume consume) {
		/*String sql = "from Consume where shopId=" + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " order by createTime desc";*/

		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where shopId=");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.find(sb.toString());
		return objcts;
	}

	public List<Object> getConsumesIndex(int index, Consume consume) {
		/*String sql = "from Consume where shopId=" + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " order by createTime desc";*/

		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where shopId=");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.findPage(index, sb.toString());
		return objcts;
	}

	public int getConsumePage(Consume consume) {
		/*String sql = "from Consume where shopId=" + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where shopId=");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");

		List<Object> objcts = super.find(sb.toString());
		return objcts.size();
	}

	public List<Object> getEnterpriseConsumes(Consume consume) {
		/*String sql = "from Consume where eaId = " + consume.getEaId()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.find(sb.toString());
		return objcts;
	}

	public List<Object> getOneConsumePage(int index, Consume consume) {
		/*String sql = "from Consume where shopId = " + consume.getShopId()
				+ " and eaId = " + consume.getEaId() + " and memberId = "
				+ consume.getMemberId() + " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where shopId = ");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" and memberId = ");
		sb.append(consume.getMemberId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.findPage(index, sb.toString());
		return objcts;
	}

	public List<Object> getEnterpriseOneConsumePage(int index, Consume consume) {
		/*String sql = "from Consume where eaId = " + consume.getEaId()
				+ " and memberId = " + consume.getMemberId()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where eaId = ");
		sb.append(consume.getEaId());
		sb.append(" and memberId = ");
		sb.append(consume.getMemberId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.findPage(index, sb.toString());
		return objcts;
	}

	/**
	 * 通过memberid和eaid来查找
	 * 
	 * @param index
	 * @param consume
	 * @return
	 */
	public List<Object> getConsumeByIDAndEa(Consume consume) {
		/*String sql = "from Consume where eaId = " + consume.getEaId()
				+ " and memberId = " + consume.getMemberId()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where eaId = ");
		sb.append(consume.getEaId());
		sb.append(" and memberId = ");
		sb.append(consume.getMemberId());
		sb.append(" order by createTime desc");
		List<Object> objects = super.find(sb.toString());
		return objects;
	}

	public List<Object> getConsumesByMobile(int index, Consume consume) {
		/*String sql = "from Consume where eaId = " + consume.getEaId()
				+ " and memberMobile = " + consume.getMemberMobile()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where eaId = ");
		sb.append(consume.getEaId());
		sb.append(" and memberMobile = ");
		sb.append(consume.getMemberMobile());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.findPage(index, sb.toString());
		return objcts;
	}

	public List<Object> getConsumesByshop(Consume consume) {
		/*String sql = "from Consume where memberId=" + consume.getMemberId()
				+ " and shopId = " + consume.getShopId()
				+ " order by createTime desc";*/
		
		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where memberId=");
		sb.append(consume.getMemberId());
		sb.append(" and shopId = ");
		sb.append(consume.getShopId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.find(sb.toString());
		return objcts;
	}

	public List<Object> getConsumesByMobileAndShop(int index, Consume consume) {
		String sql = "from Consume where memberMobile="
				+ consume.getMemberMobile() + " and shopId = "
				+ consume.getShopId() + " and eaId = " + consume.getEaId()
				+ " order by createTime desc";
		List<Object> objcts = super.findPage(index, sql);
		return objcts;
	}

	public List<Object> getConsumesBymember(Consume consume) {
		/*String sql = "from Consume where memberId=" + consume.getMemberId()
				+ " order by createTime desc";*/

		StringBuilder sb = new StringBuilder();
		sb.append("from Consume where memberId=");
		sb.append(consume.getMemberId());
		sb.append(" order by createTime desc");
		List<Object> objcts = super.find(sb.toString());
		return objcts;
	}

	// 通过时间和memberId
	public List<Object> getByCreateTimeAndMemberId(int index, Consume consume,
			String beginTime, String endTime) {
		/*String sql = "";*/
		StringBuilder sb = new StringBuilder();
		if (consume.getMemberId() != null && beginTime.equals("")
				&& endTime.equals("")) {
			/*sql = " from Consume where  memberId=" + consume.getMemberId()
					+ " and  shopId=" + consume.getShopId()
					+ "order by createTime desc";*/
			
			sb.append("from Consume where  memberId=");
			sb.append(consume.getMemberId());
			sb.append(" and shopId=");
			sb.append(consume.getShopId());
			sb.append("order by createTime desc");
		}
		if (beginTime != null && !beginTime.equals("") && endTime != null
				&& !endTime.equals("") && consume.getMemberId() == null) {

			/*sql = " from Consume where  shopId=" + consume.getShopId()
					+ " and  createTime  between  '" + beginTime + "'  and  '"
					+ endTime + "' order by createTime desc";*/
			sb.setLength(0);
			sb.append("from Consume where  shopId=");
			sb.append(consume.getShopId());
			sb.append(" and createTime between '");
			sb.append(beginTime);
			sb.append("' and '");
			sb.append(endTime);
			sb.append("' order by createTime desc");
		}
		if (consume.getMemberId() != null && beginTime != null
				&& !beginTime.equals("") && endTime != null
				&& !endTime.equals("")) {
			/*sql = "from Consume where  memberId=" + consume.getMemberId()
					+ " and  shopId=" + consume.getShopId()
					+ " and createTime  between  '" + beginTime + "' and  '"
					+ endTime + "'  order by createTime desc";*/
			sb.setLength(0);
			sb.append("from Consume where memberId=");
			sb.append(consume.getMemberId());
			sb.append(" and shopId=");
			sb.append(consume.getShopId());
			sb.append(" and createTime between '");
			sb.append(beginTime);
			sb.append("' and '");
			sb.append(endTime);
			sb.append("' order by createTime desc");
		}

		List<Object> objects = super.findPage(index, sb.toString());
		return objects;
	}

	public int getByCreateTimeAndMemberIdPage(Consume consume,
			String beginTime, String endTime) {
		/*String sql = "";*/
		StringBuilder sb = new StringBuilder();
		if (consume.getMemberId() != null && beginTime.equals("")
				&& endTime.equals("")) {
			/*sql = " from Consume where  memberId=" + consume.getMemberId()
					+ " and  shopId=" + consume.getShopId()
					+ "order by createTime desc";*/
			
			sb.append("from Consume where  memberId=");
			sb.append(consume.getMemberId());
			sb.append(" and shopId=");
			sb.append(consume.getShopId());
			sb.append("order by createTime desc");
		}
		if (beginTime != null && !beginTime.equals("") && endTime != null
				&& !endTime.equals("") && consume.getMemberId() == null) {

			/*sql = " from Consume where  shopId=" + consume.getShopId()
					+ " and  createTime  between  '" + beginTime + "'  and  '"
					+ endTime + "' order by createTime desc";*/
			sb.setLength(0);
			sb.append("from Consume where shopId=");
			sb.append(consume.getShopId());
			sb.append(" and createTime between '");
			sb.append(beginTime);
			sb.append("' and '");
			sb.append(endTime);
			sb.append("' order by createTime desc");
		}
		if (consume.getMemberId() != null && beginTime != null
				&& !beginTime.equals("") && endTime != null
				&& !endTime.equals("")) {
			/*sql = "from Consume where  memberId=" + consume.getMemberId()
					+ " and  shopId=" + consume.getShopId()
					+ " and createTime  between  '" + beginTime + "' and  '"
					+ endTime + "'  order by createTime desc";*/
			sb.setLength(0);
			sb.append("from Consume where  memberId=");
			sb.append(consume.getMemberId());
			sb.append(" and shopId=");
			sb.append(consume.getShopId());
			sb.append(" and createTime between '");
			sb.append(beginTime);
			sb.append("' and '");
			sb.append(endTime);
			sb.append("' order by createTime desc");

		}
		List<Object> objects = super.find(sb.toString());
		return objects.size();

	}

	public Object getOneById(Consume consume) {
		String hql = "from Consume where id=?";
		List<Object> object = super.getObject(hql,
				new Object[] { consume.getId() });
		if (object.size() != 0) {
			return object.get(0);
		}
		else {
			return null;
		}

	}

	/**
	 * 根据时间查询
	 */
	public List<Object> getConsumePageByTime(int index, Consume consume,
			String beginTime, String endTime) {
		/*String hql = "from Consume where shopId = " + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " order by createTime desc";*/
		
		StringBuilder sb=new StringBuilder();
		String seperate = " where ";
		
		sb.append("from Consume");
		Long shopId =  consume.getShopId();
		if (shopId != null) {
			sb.append(seperate);
			sb.append("shopId = ");
			sb.append(shopId);
			seperate = " and ";
		}
		
		Long eaId = consume.getEaId();
		if (eaId != null) {
			sb.append(seperate);
			sb.append("eaId = ");
			sb.append(eaId);
			seperate = " and ";
		}
		
		if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
			sb.append(seperate);
			sb.append("createTime  between '");
			sb.append(beginTime);
			sb.append("' and '");
			sb.append(endTime);
			sb.append("'");
			seperate = " and ";
		}
		
		sb.append(" order by createTime desc");

		return findPage(index, sb.toString());
	}

	/**
	 * 根据时间查询总数
	 * 
	 * @param consume
	 */
	public List<Object> getConsumePageByTimeTotal(Consume consume,
			String beginTime, String endTime) {
		/*String hql = "from Consume where shopId = " + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " order by createTime desc";*/
		
		StringBuilder sb=new StringBuilder();
		sb.append("from Consume where shopId = ");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" order by createTime desc");

		if (beginTime != null && endTime != null && !beginTime.equals("")
				&& !endTime.equals("")) {
			/*hql = "from Consume where shopId = " + consume.getShopId()
					+ " and eaId = " + consume.getEaId()
					+ " and  createTime  between '" + beginTime + "'   and  ' "
					+ endTime + " '" + " order by createTime desc";*/
			sb.setLength(0);
			sb.append("from Consume where shopId = ");
			sb.append(consume.getShopId());
			sb.append(" and eaId = ");
			sb.append(consume.getEaId());
			sb.append(" and createTime between '");
			sb.append(beginTime);
			sb.append("' and '");
			sb.append(endTime);
			sb.append("' order by createTime desc");
		}

		return find(sb.toString());
	}

	/**
	 * 根据时间段查询
	 */
	public List<Object> getBalancePageConsumeByTime(Consume consume,
			String beginTime, String endTime) {
		/*String hql = "from Consume where shopId = " + consume.getShopId()
				+ " and eaId = " + consume.getEaId()
				+ " and  createTime  between '" + beginTime + "'   and  ' "
				+ endTime + " '" + " order by createTime desc";*/
		
		StringBuilder sb=new StringBuilder();
		sb.append("from Consume where shopId = ");
		sb.append(consume.getShopId());
		sb.append(" and eaId = ");
		sb.append(consume.getEaId());
		sb.append(" and createTime between '");
		sb.append(beginTime);
		sb.append("' and '");
		sb.append(endTime);
		sb.append("' order by createTime desc");
		return find(sb.toString());
	}

	/**
	 * 查询在某企业的消费记录中的会员
	 */
	public List<Long> findMemberIdFromConsume(Long eaId) {
		/*String sql = "SELECT DISTINCT member_id FROM log_consume WHERE ea_id = "
				+ eaId;*/
		StringBuilder sb=new StringBuilder();
		sb.append("SELECT DISTINCT member_id FROM log_consume WHERE ea_id =");
		sb.append(eaId);
		Object object = super.executeSql(sb.toString());
		@SuppressWarnings("unchecked")
		List<Object> list = (ArrayList<Object>) object;
		List<Long> memberIds = new ArrayList<Long>();
		for (Object obj : list) {
			BigInteger bi = (BigInteger) obj;
			memberIds.add(bi.longValue());
		}
		return memberIds;
	}

	/**
	 * 查询会员在某企业里的余额消费记录总数
	 */
	public Object getType1ConsumeSum(Long eaId, Long memberId) {
		String sql  = "select SUM(money) from Consume where eaId = ? and memberId = ? and type = 1";
		List<Object> objects = getObject(sql, new Object[] { eaId, memberId });
		if (objects != null && objects.size() > 0) {
			return objects.get(0);
		}
		return null;
	}
	
	/**
	 * 查询某时间段内在企业中消费记录的总数
	 */
	public Long getCountConsumeByTime(Long eaId, String begin, String end) {
		String separate = " where ";
		StringBuilder sb = new StringBuilder();
		sb.append("select count(id) from Consume");
		if (eaId != null) {
			sb.append(separate);
			sb.append("eaId = ");
			sb.append(eaId);
			separate = " and ";
		}
		if (StringUtils.isNotBlank(begin) && StringUtils.isNotBlank(end)) {
			sb.append(separate);
			sb.append("(createTime between '");
			sb.append(begin);
			sb.append("' and '");
			sb.append(end);
			sb.append("')");
			separate = " and ";
		}
		
		List<Object> objs = find(sb.toString());
		if (objs.isEmpty()) {
			return 0L;
		}
		
		return (Long) objs.get(0);
	}
	
	/**
	 * 查询某时间段内在分店中消费记录的总数
	 */
	public Long getCountConsumeByTimeAndShopId(Long shopId, String begin, String end) {
		String separate = " where ";
		StringBuilder sb = new StringBuilder();
		sb.append("select count(id) from Consume");
		if (shopId != null) {
			sb.append(separate);
			sb.append("shopId = ");
			sb.append(shopId);
			separate = " and ";
		}
		if (StringUtils.isNotBlank(begin) && StringUtils.isNotBlank(end)) {
			sb.append(separate);
			sb.append("(createTime between '");
			sb.append(begin);
			sb.append("' and '");
			sb.append(end);
			sb.append("')");
			separate = " and ";
		}
		
		List<Object> objs = find(sb.toString());
		if (objs.isEmpty()) {
			return 0L;
		}
		
		return (Long) objs.get(0);
	}
	
	/**
	 * 查询某时间段内在企业中消费金额总量
	 */
	public Double getSumConsumeByTime(Long eaId, String begin, String end) {
		String separate = " where ";
		StringBuilder sb = new StringBuilder();
		sb.append("select sum(money) from Consume");
		if (eaId != null) {
			sb.append(separate);
			sb.append("eaId = ");
			sb.append(eaId);
			separate = " and ";
		}
		if (StringUtils.isNotBlank(begin) && StringUtils.isNotBlank(end)) {
			sb.append(separate);
			sb.append("(createTime between '");
			sb.append(begin);
			sb.append("' and '");
			sb.append(end);
			sb.append("')");
			separate = " and ";
		}
		
		List<Object> objs = find(sb.toString());
		if (objs.isEmpty()) {
			return 0D;
		}
		
		Object obj = objs.get(0);
		if (obj == null) {
			return 0D;
		}
		
		return (Double) obj;
	}
	
	/**
	 * 查询某时间段内在分店中消费金额总量
	 */
	public Double getSumConsumeByTimeAndShopId(Long shopId, String begin, String end) {
		String separate = " where ";
		StringBuilder sb = new StringBuilder();
		sb.append("select sum(money) from Consume");
		if (shopId != null) {
			sb.append(separate);
			sb.append("shopId = ");
			sb.append(shopId);
			separate = " and ";
		}
		if (StringUtils.isNotBlank(begin) && StringUtils.isNotBlank(end)) {
			sb.append(separate);
			sb.append("(createTime between '");
			sb.append(begin);
			sb.append("' and '");
			sb.append(end);
			sb.append("')");
			separate = " and ";
		}
		
		List<Object> objs = find(sb.toString());
		if (objs.isEmpty()) {
			return 0D;
		}
		
		Object obj = objs.get(0);
		if (obj == null) {
			return 0D;
		}
		
		return (Double) obj;
	}

	public void deleteById(Consume consume) {
		super.delete(consume);
	}

}
